package work.mediway.mdm.biz.helper;

import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.mediway.hos.common.utils.spring.SpringUtils;
import com.mediway.hos.redis.util.RedisUtil;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import work.mediway.mdm.biz.entity.dict.CtCodeSystem;
import work.mediway.mdm.biz.service.dict.CtCodeSystemService;
import work.mediway.mdm.biz.vo.dict.DictFieldVO;
import work.mediway.mdm.core.constant.MdmConstant;
import work.mediway.mdm.core.entity.IndexInfo;
import work.mediway.mdm.core.entity.TableForeignKey;
import work.mediway.mdm.core.enums.StatusWhetherEnum;
import work.mediway.mdm.core.exception.BizException;
import work.mediway.mdm.core.exception.DataException;
import work.mediway.mdm.core.util.SqlSessionUtil;

/**
 * @author zhongyj <1126834403@qq.com><br/>
 * @date 2021/1/28
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class BusinessDatabaseHelper {

    public List<DictFieldVO> getColumn(String tableCode) {
        return getColumn(SqlSessionUtil.getSqlSession(SpringUtils.getBean(SqlSessionTemplate.class)).getConnection(), null, tableCode);
    }

    public List<DictFieldVO> getColumn(Connection connection, String tableCode) {
        return getColumn(connection, null, tableCode);
    }

    public List<DictFieldVO> getColumn(String tableCode, boolean showForeign) {
        List<DictFieldVO> column = this.getColumn(tableCode);
        if (showForeign) {
            List<TableForeignKey> foreignKey = this.getForeignKey(SpringUtils.getBean(SqlSessionTemplate.class), tableCode);
            Map<String, TableForeignKey> foreignMap = MapUtil.newHashMap();
            List<String> foreignList = CollUtil.newArrayList();
            for (TableForeignKey tableForeignKey : foreignKey) {
                foreignMap.put(tableForeignKey.getPkColumnName(), tableForeignKey);
                foreignList.add(tableForeignKey.getPkColumnName());
            }
            for (DictFieldVO vo : column) {
                if (CollUtil.contains(foreignList, vo.getFieldCode())) {
                    vo.setFkTableName(foreignMap.get(vo.getFieldCode()).getFkTableName());
                    vo.setFkColumnName(foreignMap.get(vo.getFieldCode()).getFkColumnName());
                }
            }
        }
        return column;
    }

    public List<DictFieldVO> getColumn(Connection connection, String schema, String tableCode) {
        tableCode = StrUtil.removePrefixIgnoreCase(tableCode, MdmConstant.SCHEMA_NAME_);
        List<DictFieldVO> query = CollUtil.newArrayList();

        String cacheKey = StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "column", StrUtil.join(StrUtil.DASHED, schema, tableCode));
        Object cache = RedisUtil.get(cacheKey);
        if (ObjectUtil.isNotEmpty(cache)) {
            query = JSONUtil.toList(StrUtil.toString(cache), DictFieldVO.class);
            log.debug("从缓存中获取表: {} 的列信息", StrUtil.join(StrUtil.DASHED, schema, tableCode));
        } else {
            //从conn中获取数据库的元数据
            DatabaseMetaData databaseMetaData;
            try {
                databaseMetaData = connection.getMetaData();
                DatabaseMetaData md = connection.getMetaData();
                log.debug("Database ProductName = " + md.getDatabaseProductName());
                ResultSet resultSet = databaseMetaData.getColumns(connection.getCatalog(), schema, tableCode, null);
                while (resultSet.next()) {
                    //遍历表字段所有元数据
                    DictFieldVO fieldVO = new DictFieldVO();
                    fieldVO.setTableName(StrUtil.toString(resultSet.getObject("TABLE_NAME")));
                    fieldVO.setSqlType(StrUtil.toString(resultSet.getObject("TYPE_NAME")));
                    fieldVO.setFieldCode(StrUtil.toString(resultSet.getObject("COLUMN_NAME")));
                    fieldVO.setColumnSize(StrUtil.toString(resultSet.getObject("COLUMN_SIZE")));
                    fieldVO.setFieldDesc(ObjectUtil.isEmpty(resultSet.getObject("REMARKS")) ? StrUtil.toString(resultSet.getObject("COLUMN_NAME")) : StrUtil.toString(resultSet.getObject("REMARKS")));
                    if (!StrUtil.equalsAnyIgnoreCase("Apache Hive", md.getDatabaseProductName())) {
                        fieldVO.setInitialExpression(StrUtil.toString(resultSet.getObject("COLUMN_DEF")));
                        fieldVO.setAvailableValue(StrUtil.toString(resultSet.getObject("COLUMN_DEF")));
                        fieldVO.setIsAutoIncrement(StrUtil.toString(resultSet.getObject("IS_AUTOINCREMENT")));
                        fieldVO.setRequired(!StrUtil.equals("YES", StrUtil.toString(resultSet.getObject("IS_NULLABLE"))));
                    }
                    query.add(fieldVO);
                }
                if (CollUtil.isNotEmpty(query)) {
                    RedisUtil.set(cacheKey, JSONUtil.toJsonStr(query));
                }
            } catch (SQLException e) {
                e.printStackTrace();
                throw new BizException(StrUtil.format("查询表[{}]列信息失败,失败信息: {}", tableCode, e));
            }
        }

        // 查询表主键
        List<String> primaryKey = getPrimaryKey(connection, schema, tableCode);
        for (DictFieldVO fieldVO : query) {
            fieldVO.setTableName(tableCode);
            fieldVO.setCurrTable(StatusWhetherEnum.STATUS_Y.getStatusCode());
            if (CollUtil.contains(primaryKey, fieldVO.getFieldCode())) {
                fieldVO.setPrimaryKey(StatusWhetherEnum.STATUS_Y.getStatusCode());
            } else {
                fieldVO.setPrimaryKey(StatusWhetherEnum.STATUS_N.getStatusCode());
            }
            if (StrUtil.isNotBlank(fieldVO.getAvailableValue()) && StrUtil.containsIgnoreCase(fieldVO.getAvailableValue(), MdmConstant.VALUES_LIST)) {
                String subAfter = StrUtil.subAfter(fieldVO.getAvailableValue(), MdmConstant.VALUES_LIST, false);
                fieldVO.setAvailableValues(StrUtil.split(StrUtil.subBetween(subAfter, StringPool.QUOTE), StringPool.COMMA, -1, true, true));

                String subBetween = StrUtil.subBetween(fieldVO.getAvailableValue(), MdmConstant.DISPLAY_LIST, MdmConstant.VALUES_LIST);
                fieldVO.setAvailableValuesDesc(StrUtil.split(StrUtil.subBetween(subBetween, StringPool.QUOTE), StringPool.COMMA, -1, true, true));
            }
        }
        return query;
    }


    public List<TableForeignKey> getForeignKey(String tableCode) {
        return getForeignKey(SpringUtils.getBean(SqlSessionTemplate.class), tableCode);
    }

    public List<TableForeignKey> getForeignKey(SqlSessionTemplate sqlSessionTemplate, String tableCode) {
        if (ObjectUtil.isEmpty(sqlSessionTemplate)) {
            sqlSessionTemplate = SpringUtils.getBean(SqlSessionTemplate.class);
        }
        return getForeignKey(SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection(), null, tableCode);
    }

    public List<TableForeignKey> getForeignKey(Connection connection, String schema, String tableCode) {
        List<TableForeignKey> column = CollUtil.newArrayList();
        String cacheKey = StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "foreign_key", StrUtil.join(StrUtil.DASHED, schema, tableCode));
        Object cache = RedisUtil.get(cacheKey);
        if (ObjectUtil.isNotEmpty(cache)) {
            column = JSONUtil.toList(StrUtil.toString(cache), TableForeignKey.class);
            log.debug("从缓存中获取表: {} 的外键信息", StrUtil.join(StrUtil.DASHED, schema, tableCode));
        } else {
            //表字段信息
            try {
                ResultSet resultSet = connection.getMetaData().getImportedKeys(connection.getCatalog(), schema, tableCode);
                while (resultSet.next()) {
                    List<String> field = getColumn(tableCode).stream().map(DictFieldVO::getFieldCode).collect(Collectors.toList());
                    TableForeignKey foreignKey = new TableForeignKey();
                    foreignKey.setFkTableSchema(StrUtil.isBlank(resultSet.getString("PKTABLE_SCHEM")) ? null : resultSet.getString("PKTABLE_SCHEM"));
                    foreignKey.setFkTableName(resultSet.getString("PKTABLE_NAME"));
                    foreignKey.setFkColumnName(resultSet.getString("PKCOLUMN_NAME"));
                    foreignKey.setPkTableSchema(StrUtil.isBlank(resultSet.getString("FKTABLE_SCHEM")) ? null : resultSet.getString("FKTABLE_SCHEM"));
                    foreignKey.setPkTableName(resultSet.getString("FKTABLE_NAME"));
                    foreignKey.setPkColumnName(resultSet.getString("FKCOLUMN_NAME"));
                    foreignKey.setFkName(resultSet.getString("FK_NAME"));
                    if (CollUtil.contains(field, foreignKey.getPkColumnName())) {
                        column.add(foreignKey);
                    }
                }
                if (CollUtil.isNotEmpty(column)) {
                    RedisUtil.set(cacheKey, JSONUtil.toJsonStr(column));
                }
            } catch (SQLException e) {
                throw new BizException(StrUtil.format("查询表[{}]外键键信息失败,失败信息: {}", tableCode, e));
            }
        }
        return column;
    }


    public List<String> getPrimaryKey(String tableCode) {
        return getPrimaryKey(SpringUtils.getBean(SqlSessionTemplate.class), tableCode);
    }

    public List<String> getPrimaryKey(SqlSessionTemplate sqlSessionTemplate, String tableCode) {
        return getPrimaryKey(SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection(), null, tableCode);
    }

    public List<String> getPrimaryKey(Connection connection, String schema, String tableCode) {
        List<String> column = CollUtil.newArrayList();
        String cacheKey = StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "primary_key", StrUtil.join(StrUtil.DASHED, schema, tableCode));
        Object cache = RedisUtil.get(cacheKey);
        if (ObjectUtil.isNotEmpty(cache)) {
            column = JSONUtil.toList(StrUtil.toString(cache), String.class);
            log.debug("从缓存中获取表: {} 的主键信息", StrUtil.join(StrUtil.DASHED, schema, tableCode));
        } else {
            //表字段信息
            try {
                ResultSet resultSet = connection.getMetaData().getPrimaryKeys(connection.getCatalog(), schema, tableCode);
                while (resultSet.next()) {
                    column.add(resultSet.getString("COLUMN_NAME"));
                }
                if (CollUtil.isNotEmpty(column)) {
                    RedisUtil.set(cacheKey, JSONUtil.toJsonStr(column));
                }
            } catch (SQLException e) {
                throw new BizException(StrUtil.format("查询表[{}]主键信息失败,失败信息: {}", tableCode, e));
            }
        }

        return column;
    }


    public List<IndexInfo> getIndexInfo(String tableCode) {
        return getIndexInfo(SqlSessionUtil.getSqlSession(SpringUtils.getBean(SqlSessionTemplate.class)).getConnection(), tableCode);
    }

    public List<IndexInfo> getIndexInfo(Connection connection, String tableCode) {
        List<IndexInfo> indexInfos = CollUtil.newArrayList();
        String cacheKey = StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "index", tableCode);
        Object cache = RedisUtil.get(cacheKey);
        if (ObjectUtil.isNotEmpty(cache)) {
            indexInfos = JSONUtil.toList(StrUtil.toString(cache), IndexInfo.class);
            log.debug("从缓存中获取表: {} 的索引信息", tableCode);
        } else {
            try {
                ResultSet resultSet = connection.getMetaData().getIndexInfo(connection.getCatalog(), null, tableCode, false, false);
                while (resultSet.next()) {
                    if (StrUtil.equalsAnyIgnoreCase("PRIMARY", resultSet.getString("INDEX_NAME"))) {
                        continue;
                    }
                    IndexInfo indexInfo = new IndexInfo();
                    indexInfo.setColumnName(resultSet.getString("COLUMN_NAME"));
                    indexInfo.setTableName(resultSet.getString("TABLE_NAME"));
                    indexInfo.setIndexName(resultSet.getString("INDEX_NAME"));
                    indexInfos.add(indexInfo);
                }
                if (CollUtil.isNotEmpty(indexInfos)) {
                    RedisUtil.set(cacheKey, JSONUtil.toJsonStr(indexInfos));
                }
            } catch (SQLException e) {
                throw new BizException(StrUtil.format("查询表[{}]索引信息失败,失败信息: {}", tableCode, e));
            }
        }
        return indexInfos;
    }

    /**
     * 推算目标系统中的时间戳字段
     *
     * @param tableCode String
     * @return long
     */
    public String calculateTableDateField(Connection connection, String schema, String tableCode) {
        String dateField = null;
        List<DictFieldVO> column = getColumn(connection, schema, tableCode);
        for (DictFieldVO dictFieldVO : column) {
            if (StrUtil.containsAnyIgnoreCase(dictFieldVO.getSqlType(), "DATE")
                    && (StrUtil.contains(dictFieldVO.getFieldCode(), "update_")
                    || StrUtil.equals(dictFieldVO.getFieldCode(), "UpdateDate")
                    || StrUtil.contains(dictFieldVO.getFieldCode(), "create_"))) {
                dateField = dictFieldVO.getFieldCode();
                break;
            }
        }
        if (StrUtil.isBlank(dateField)) {
            throw new DataException(StrUtil.format("未找到表 = {} 对应的时间戳字段", tableCode));
        }
        return dateField;
    }

    /**
     * 表缓存初始化
     */
//    @PostConstruct
    public void initTableCache() {
        List<CtCodeSystem> codeSystems = SpringUtils.getBean(CtCodeSystemService.class).list();
        Connection connection = SqlSessionUtil.getSqlSession(SpringUtils.getBean(SqlSessionTemplate.class)).getConnection();
        for (CtCodeSystem codeSystem : codeSystems) {
            log.debug("缓存表信息: {}", codeSystem.getCodeSystemCode());
            List<DictFieldVO> column = getColumn(connection, codeSystem.getCodeSystemCode());
            List<TableForeignKey> foreignKey = getForeignKey(connection, null, codeSystem.getCodeSystemCode());
            List<String> primaryKey = getPrimaryKey(connection, null, codeSystem.getCodeSystemCode());
            List<IndexInfo> indexInfo = getIndexInfo(connection, codeSystem.getCodeSystemCode());
            if (CollUtil.isNotEmpty(column)) {
                RedisUtil.set(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "column", codeSystem.getCodeSystemCode()), JSONUtil.toJsonStr(column));
            }
            if (CollUtil.isNotEmpty(foreignKey)) {
                RedisUtil.set(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "foreign_key", codeSystem.getCodeSystemCode()), JSONUtil.toJsonStr(foreignKey));
            }
            if (CollUtil.isNotEmpty(primaryKey)) {
                RedisUtil.set(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "primary_key", codeSystem.getCodeSystemCode()), JSONUtil.toJsonStr(primaryKey));
            }
            if (CollUtil.isNotEmpty(indexInfo)) {
                RedisUtil.set(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "index", codeSystem.getCodeSystemCode()), JSONUtil.toJsonStr(indexInfo));
            }
        }
    }

    public void cleanTableCache(String tableCode) {
        RedisUtil.hdel(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "column", tableCode));
        RedisUtil.hdel(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "foreign_key", tableCode));
        RedisUtil.hdel(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "primary_key", tableCode));
        RedisUtil.hdel(StrUtil.format(MdmConstant.RedisConst.TABLE_INFO_CACHE, "index", tableCode));
    }

    // ============================================================================= cache ========================================================================= //

    /*public List<DictFieldVO> getCacheColumn(String tableCode) {
        tableCode = StrUtil.removePrefixIgnoreCase(tableCode, MdmConstant.SCHEMA_NAME_);
        // 查询表对应的类
        DictListDTO dto = new DictListDTO();
        dto.setDictParam(StrUtil.removePrefixIgnoreCase(tableCode, MdmConstant.SCHEMA_NAME_));
        CtCodeSystem codeSystem = this.codeSystemService.getOne(Wrappers.lambdaQuery(CtCodeSystem.class).eq(CtCodeSystem::getCodeSystemCode, tableCode));

        String saveTableClass;
        if (ObjectUtil.isEmpty(codeSystem)) {
            saveTableClass = MdmConstant.TABLE_PREFIX + StrUtil.removeAll(tableCode, StringPool.UNDERSCORE);
        } else {
            saveTableClass = codeSystem.getSaveTableClass();
        }
        // 查询表字段和描述
        List<DictFieldVO> query = this.jdbcTemplate
                .query("SELECT SqlFieldName AS fieldCode, Description AS fieldDesc, Required AS required, Type AS sqlType, InitialExpression AS initialExpression, Parameters AS availableValue FROM %Dictionary.PropertyDefinition WHERE parent = ?"
                        , new DictFieldVO()
                        , saveTableClass);
        // 查询表主键
        List<String> primaryKey = getCachePrimaryKey(sessionTemplate, MdmConstant.SCHEMA_NAME, tableCode);

        for (DictFieldVO fieldVO : query) {
            fieldVO.setTableName(tableCode);
            fieldVO.setCurrTable(StatusWhetherEnum.STATUS_Y.getStatusCode());
            if (CollUtil.contains(primaryKey, fieldVO.getFieldCode())) {
                fieldVO.setPrimaryKey(StatusWhetherEnum.STATUS_Y.getStatusCode());
            } else {
                fieldVO.setPrimaryKey(StatusWhetherEnum.STATUS_N.getStatusCode());
            }
            if (StrUtil.isNotBlank(fieldVO.getAvailableValue()) && StrUtil.containsIgnoreCase(fieldVO.getAvailableValue(), MdmConstant.VALUES_LIST)) {
                String subAfter = StrUtil.subAfter(fieldVO.getAvailableValue(), MdmConstant.VALUES_LIST, false);
                fieldVO.setAvailableValues(StrUtil.split(StrUtil.subBetween(subAfter, StringPool.QUOTE), StringPool.COMMA, -1, true, true));

                String subBetween = StrUtil.subBetween(fieldVO.getAvailableValue(), MdmConstant.DISPLAY_LIST, MdmConstant.VALUES_LIST);
                fieldVO.setAvailableValuesDesc(StrUtil.split(StrUtil.subBetween(subBetween, StringPool.QUOTE), StringPool.COMMA, -1, true, true));
            }
        }
//        caffeine.put(TABLE_COLUMN + tableCode, query);
        return query;
    }

    public List<DictFieldVO> getCacheColumn(String tableCode, boolean showForeign) {
        List<DictFieldVO> column = this.getCacheColumn(tableCode);
        if (showForeign) {
            List<TableForeignKey> foreignKey = this.getCacheForeignKey(sessionTemplate, MdmConstant.SCHEMA_NAME, tableCode);
            Map<String, TableForeignKey> foreignMap = MapUtil.newHashMap();
            List<String> foreignList = CollUtil.newArrayList();
            for (TableForeignKey tableForeignKey : foreignKey) {
                foreignMap.put(tableForeignKey.getPkColumnName(), tableForeignKey);
                foreignList.add(tableForeignKey.getPkColumnName());
            }
            for (DictFieldVO vo : column) {
                if (CollUtil.contains(foreignList, vo.getFieldCode())) {
                    vo.setFkTableName(foreignMap.get(vo.getFieldCode()).getFkTableName());
                    vo.setFkColumnName(foreignMap.get(vo.getFieldCode()).getFkColumnName());
                }
            }
        }
        return column;
    }

    public List<TableForeignKey> getCacheForeignKey(SqlSessionTemplate sqlSessionTemplate, String schema, String tableCode) {
        List<TableForeignKey> column = CollUtil.newArrayList();
        Connection connection = SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection();
        //表字段信息
        try {
            ResultSet resultSet = connection.getMetaData().getImportedKeys(connection.getCatalog(), schema, tableCode);
            while (resultSet.next()) {
                List<String> field = getCacheColumn(tableCode).stream().map(DictFieldVO::getFieldCode).collect(Collectors.toList());
                TableForeignKey foreignKey = new TableForeignKey();
                foreignKey.setFkTableSchema(resultSet.getString("PKTABLE_SCHEM"));
                foreignKey.setFkTableName(resultSet.getString("PKTABLE_NAME"));
                foreignKey.setFkColumnName(resultSet.getString("PKCOLUMN_NAME"));
                foreignKey.setPkTableSchema(resultSet.getString("FKTABLE_SCHEM"));
                foreignKey.setPkTableName(resultSet.getString("FKTABLE_NAME"));
                foreignKey.setPkColumnName(resultSet.getString("FKCOLUMN_NAME"));
                if (CollUtil.contains(field, foreignKey.getPkColumnName())) {
                    column.add(foreignKey);
                }
            }
        } catch (SQLException e) {
            throw new BizException("SQL执行异常", e);
        }
        return column;
    }

    public List<String> getCachePrimaryKey(SqlSessionTemplate sqlSessionTemplate, String schema, String tableCode) {
        List<String> column = CollUtil.newArrayList();
        Connection connection = SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection();
        //表字段信息
        try {
            ResultSet resultSet = connection.getMetaData().getPrimaryKeys(connection.getCatalog(), schema, tableCode);
            while (resultSet.next()) {
                column.add(resultSet.getString("COLUMN_NAME"));
            }
        } catch (SQLException e) {
            throw new BizException("SQL执行异常", e);
        }
        return column;
    }*/

}
